﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Web.Admin.Report
{
    public partial class RWBExchange : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Qdtstart.Value = DateTime.Now.AddMonths(-1).ToShortDateString();
                Qdtend.Value = DateTime.Now.AddDays(1).ToShortDateString();
            }
        }
        private void DataBind()
        {

            DataTable dt = GetWBReport();
            Repeater1.DataSource = dt;
            Repeater1.DataBind();



            DataTable dt2 = GetTotalReport();
            Repeater2.DataSource = dt2;
            Repeater2.DataBind();
            //合计数据


        }

        private DataTable GetTotalReport()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  SELECT  D.strName AS  VarietyID,SUM(GoodCount) AS GoodCount,SUM(VarietyCount)AS VarietyCount,");
            strSql.Append("   SUM(A.Money_DuiHuan) AS Money_DuiHuan,SUM(A.VarietyInterest) AS VarietyInterest");
            strSql.Append("  FROM dbo.GoodExchange A INNER JOIN dbo.WB B ON A.WBID=B.ID");
            strSql.Append("  INNER JOIN dbo.Dep_StorageInfo C ON A.Dep_SID=C.ID");
            strSql.Append("   INNER JOIN dbo.StorageVariety D ON C.VarietyID=D.ID");
            strSql.Append("  INNER JOIN dbo.Good E ON A.GoodID=E.ID");
            strSql.Append("   INNER JOIN dbo.Depositor F ON A.Dep_AccountNumber=F.AccountNumber");
            strSql.Append("  where 1=1 and B.ISSimulate=0");
            if (ReportType.Value == "P")
            {
                strSql.Append("  and A.ISReturn=0");
            }
            else
            {
                strSql.Append("  and A.ISReturn!=0");
            }

            if (TradeWBID.Value == "1")
            {
                strSql.Append("   AND A.WBID=F.WBID");
            }
            else if (TradeWBID.Value == "2")
            {
                strSql.Append("   AND A.WBID!=F.WBID");
            }

            if (Request.Form["QWBID"] != null && Request.Form["QWBID"].ToString() != "")
            {
                strSql.Append("   AND B.ID =" + Request.Form["QWBID"].ToString());
            }
            if (Qdtstart.Value.Trim() != "")
            {
                strSql.Append("   AND A.dt_Exchange> '" + Qdtstart.Value.Trim() + "'");
            }
            if (Qdtend.Value.Trim() != "")
            {
                strSql.Append("   AND A.dt_Exchange < '" + Qdtend.Value.Trim() + "'");
            }

            strSql.Append("   GROUP BY  D.strName");
            strSql.Append("  ");


            DataTable dt2 = SQLHelper.ExecuteDataTable(strSql.ToString());
            return dt2;
        }

        private DataTable GetWBReport()
        {

            //获取存粮信息
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  SELECT W.ID as WBID, W.strName as WBName, B.ID as TradeWBID, B.strName AS TradeWBName,D.ID as VarietyID, D.strName AS  VarietyName,E.ID as GoodID, E.strName AS  GoodName, SUM(GoodCount) AS GoodCount,SUM(VarietyCount)AS VarietyCount,");
            strSql.Append("   SUM(A.Money_DuiHuan) AS Money_DuiHuan,SUM(A.VarietyInterest) AS VarietyInterest");
            strSql.Append("  FROM dbo.GoodExchange A INNER JOIN dbo.WB B ON A.WBID=B.ID");
            strSql.Append("  INNER JOIN dbo.Dep_StorageInfo C ON A.Dep_SID=C.ID");
            strSql.Append("   INNER JOIN dbo.StorageVariety D ON C.VarietyID=D.ID");
            strSql.Append("  INNER JOIN dbo.Good E ON A.GoodID=E.ID");
            strSql.Append("   INNER JOIN dbo.Depositor F ON A.Dep_AccountNumber=F.AccountNumber");
            strSql.Append("   INNER JOIN dbo.WB W ON F.WBID=W.ID");
            strSql.Append("  ");
            strSql.Append("  where 1=1 and B.ISSimulate=0");
            if (ReportType.Value == "P")
            {
                strSql.Append("  and A.ISReturn=0");
            }
            else
            {
                strSql.Append("  and A.ISReturn!=0");
            }

            if (TradeWBID.Value == "1")
            {
                strSql.Append("   AND A.WBID=F.WBID");
            }
            else if (TradeWBID.Value == "2")
            {
                strSql.Append("   AND A.WBID!=F.WBID");
            }
            if (Request.Form["QWBID"] != null && Request.Form["QWBID"].ToString() != "")
            {
                strSql.Append("   AND W.ID =" + Request.Form["QWBID"].ToString());
            }
            if (Qdtstart.Value.Trim() != "")
            {
                strSql.Append("   AND A.dt_Exchange> '" + Qdtstart.Value.Trim() + "'");
            }
            if (Qdtend.Value.Trim() != "")
            {
                strSql.Append("   AND A.dt_Exchange < '" + Qdtend.Value.Trim() + "'");
            }

            strSql.Append("   GROUP BY W.ID, W.strName,B.ID, B.strName,E.ID, E.strName,D.ID,D.strName");
            strSql.Append("  ");


            DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString());
            return dt;
        }
        protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
        {
            DataBind();
        }


        DataTable GetOutPutTable()
        {
            //获取存粮信息
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  SELECT W.strName as WBName,  B.strName AS TradeWBName, D.strName AS  VarietyName, E.strName AS  GoodName, SUM(GoodCount) AS GoodCount,SUM(VarietyCount)AS VarietyCount,");
            strSql.Append("   SUM(A.Money_DuiHuan) AS Money_DuiHuan,SUM(A.VarietyInterest) AS VarietyInterest");
            strSql.Append("  FROM dbo.GoodExchange A INNER JOIN dbo.WB B ON A.WBID=B.ID");
            strSql.Append("  INNER JOIN dbo.Dep_StorageInfo C ON A.Dep_SID=C.ID");
            strSql.Append("   INNER JOIN dbo.StorageVariety D ON C.VarietyID=D.ID");
            strSql.Append("  INNER JOIN dbo.Good E ON A.GoodID=E.ID");
            strSql.Append("   INNER JOIN dbo.Depositor F ON A.Dep_AccountNumber=F.AccountNumber");
            strSql.Append("   INNER JOIN dbo.WB W ON F.WBID=W.ID");
            strSql.Append("  ");
            strSql.Append("  where 1=1 and B.ISSimulate=0");
            if (ReportType.Value == "P")
            {
                strSql.Append("  and A.ISReturn=0");
            }
            else
            {
                strSql.Append("  and A.ISReturn!=0");
            }

            if (TradeWBID.Value == "1")
            {
                strSql.Append("   AND A.WBID=F.WBID");
            }
            else if (TradeWBID.Value == "2")
            {
                strSql.Append("   AND A.WBID!=F.WBID");
            }
            if (Request.Form["QWBID"] != null && Request.Form["QWBID"].ToString() != "")
            {
                strSql.Append("   AND W.ID =" + Request.Form["QWBID"].ToString());
            }
            if (Qdtstart.Value.Trim() != "")
            {
                strSql.Append("   AND A.dt_Exchange> '" + Qdtstart.Value.Trim() + "'");
            }
            if (Qdtend.Value.Trim() != "")
            {
                strSql.Append("   AND A.dt_Exchange < '" + Qdtend.Value.Trim() + "'");
            }

            strSql.Append("   GROUP BY  W.strName, B.strName, E.strName,D.strName");
            strSql.Append("  ");


            DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString());
            return dt;
        }
   
        private void outputExcel()
        {
            DataTable dt = GetOutPutTable();
            if (dt == null || dt.Rows.Count == 0)
            {
                Fun.Alert("请先检索要查询的信息后在导出！");
                return;
            }

            string fileName = "兑换报表" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + ".xls";

            HttpResponse resp;
            resp = Page.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            string colHeaders = "", ls_item = "", ls_Bottom = "";

            DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
            int i = 0;
            int cl = dt.Columns.Count;

            colHeaders += "储户网点\t交易网点\t业务类型\t交易商品\t交易量\t折合原粮\t折合现金\t利息\n";
            resp.Write(colHeaders);
            //向HTTP输出流中写入取得的数据信息

            //逐行处理数据  
            foreach (DataRow row in myRow)
            {
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据    
                for (i = 0; i < cl; i++)
                {
                    if (i == (cl - 1))//最后一列，加\n
                    {
                        ls_item += row[i].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "\t";
                    }
                }
                resp.Write(ls_item);
                ls_item = "";

            }


            //添加第二张表格的内容
            DataTable dt2 = GetTotalReport();
            DataRow[] myRow2 = dt2.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的

            int cl2 = dt2.Columns.Count;

            colHeaders = "总计\n";
           
            resp.Write(colHeaders);
            //向HTTP输出流中写入取得的数据信息
            ls_item = "";
            //逐行处理数据  
            foreach (DataRow row in myRow2)
            {
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据    
                for (int j = 0; j < cl2; j++)
                {
                    if (j == (cl2 - 1))//最后一列，加\n
                    {
                        ls_item += row[j].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[j].ToString() + "\t";
                    }
                }
                resp.Write(ls_item);
                ls_item = "";

            }



            resp.End();
        }

        protected void btnOutPut_Click(object sender, EventArgs e)
        {
            outputExcel();
        }

    }
}